using System;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Net.NetworkInformation;
using System.Management;
using System.DirectoryServices;
using System.Net.Mail;

//---------------------------------------------------------------------------------
//This page is called from the SQL Server SQL Agent Scheduled Job. 
//SELECT [EnterpriseDBA].[dbo].[WebRequest] ('http://WEBSERVER/ScheduledTask.aspx?Task=LoadInstanceStatus')
//---------------------------------------------------------------------------------
    
public partial class ScheduledTask : System.Web.UI.Page
{
    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");

    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            ///////////////////////////////////////////////////////////////////////////
            // ScheduledTask.aspx?Task=LoadRepository
            ///////////////////////////////////////////////////////////////////////////
            if (Request.QueryString["Task"].ToString() == "LoadRepository")
            {
                try
                {
                    Utility.LoadRepository("All");
                }
                catch (Exception errLoadRepository1)
                {
                    Utility.DBANotificationAlert("LoadRepository Task Failed: " + errLoadRepository1.Message.ToString(), "","Medium");
                }                
            }

            ///////////////////////////////////////////////////////////////////////////
            // ScheduledTask.aspx?Task=LoadInstanceStatus
            ///////////////////////////////////////////////////////////////////////////
            if (Request.QueryString["Task"].ToString() == "LoadInstanceStatus")
            {
                try
                {
                    Utility.LoadInstanceStatus("All");
                    //Utility.LoadInstanceStatus(@"HOUSPSTG004\SPSTGSQL");
                }
                catch (Exception errLoadRepository1)
                {
                    Utility.DBANotificationAlert("LoadInstanceStatus Task Failed: " + errLoadRepository1.Message.ToString(), "", "Medium");
                }
            }                               

            ///////////////////////////////////////////////////////////////////////////
            // ScheduledTask.aspx?Task=DatabaseVerificationEMail
            ///////////////////////////////////////////////////////////////////////////
            if (Request.QueryString["Task"].ToString() == "DatabaseVerificationEMail")
            {
                if (Session["sHTTP_PATH"] == null)
                {
                    Session["sHTTP_PATH"] = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='HTTP_PATH'").ToString();
                }

                try
                {
                    string connectionString = ConfigurationManager.AppSettings["Application_DataConnString"];
                    SqlConnection cn = new SqlConnection(connectionString);
                    cn.Open();

                    SqlDataAdapter a1 = new SqlDataAdapter(@"SELECT DISTINCT(LOWER(d.PrimaryContactEMail)) AS PrimaryContactEMail FROM [Database] d WHERE LastVerified < getutcdate()-180 OR LastVerified IS NULL AND DatabaseName NOT IN ('master','model','msdb','tempdb') ORDER BY PrimaryContactEMail", cn);
                    DataSet s1 = new DataSet();

                    a1.Fill(s1);

                    foreach (DataRow dr1 in s1.Tables[0].Rows)
                    {
                        try
                        {
                            MailMessage msg = new MailMessage();

                            msg.To.Add(dr1["PrimaryContactEMail"].ToString());
                            msg.Subject = "Database Verification Request";
                            msg.Body =  "You are receiving this email because you are the designated contact for a database that has not been verified within the last 6 months." + " \r\n" +
                                        "" + " \r\n" +
                                        "Please click the following link to verify designated databases." + " \r\n" +
                                        "" + " \r\n" +
                                        "" + Session["sHTTP_PATH"].ToString() + "DatabaseVerification.aspx?PrimaryContactEMail=" + dr1["PrimaryContactEMail"].ToString() + " \r\n" +
                                        "" + " \r\n" +
                                        "To ensure accurate contact information databases require verification every 6 months." + " \r\n" +
                                        "-----------------------------------------------------" + " \r\n" +
                                        "END OF MESSAGE" + " \r\n";

                            SmtpClient client = new SmtpClient();
                            client.Send(msg);
                        }
                        catch (Exception err)
                        {
                            DbCommand dbCommand1 = db.GetSqlStringCommand("iuAlert @AlertPriority='Medium', @AlertInstanceName='Utility',@AlertName='" + err.Message.ToString()+"'");
                            db.ExecuteNonQuery(dbCommand1);
                        }
                    }
                }
                catch (Exception errDatabaseVerificationEMail)
                {
                    DbCommand dbCommand1 = db.GetSqlStringCommand("iuAlert @AlertPriority='Medium', @AlertInstanceName='Utility',@AlertName='" + errDatabaseVerificationEMail.Message.ToString()+"'");
                    db.ExecuteNonQuery(dbCommand1);
                }
            }

            ///////////////////////////////////////////////////////////////////////////
            // ScheduledTask.aspx?Task=DBAAlertEMail
            ///////////////////////////////////////////////////////////////////////////
            if (Request.QueryString["Task"].ToString() == "DBAAlertEMail")
            {
                try
                {
                    Utility.DBAAlertEMailSummary();
                }
                catch (Exception errDBAAlertEMail)
                {
                    Utility.DBANotificationAlert("DBAAlert EMail Failure: " + errDBAAlertEMail.Message.ToString(), "", "Medium");
                }
            }

        }
        catch (Exception errScheduledTaskFailure1)
        {
            Utility.DBANotificationAlert("Scheduled Task Failure: " + errScheduledTaskFailure1.Message.ToString(), "", "Medium");
        }
    }    
}